import numpy as np
import xlwings as xw
import pandas as pd
from itertools import product
import matplotlib.pyplot as plt
from faker import Faker
import os
from xlwings.utils import rgb_to_int


def getMethod():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    d = {'a': 1, 'b': 2}
    sht.range('A1').value = d
    print(sht.range('A1').options(np.array, expand='table').value)
    return wb
    # [['a' '1.0']
    #  ['b' '2.0']]

def getMethod1():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    sht.range('A1').value = np.eye(10)  # 对角矩阵
    print(sht.range('A1').options(np.array, expand='table').value)
    return wb
    # [[1. 0. 0.]
    #  [0. 1. 0.]
    #  [0. 0. 1.]]

def getMethod2():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])
    sht.range('A1').value = df  # 写入DataFrame
    print(sht.range('A1').options(pd.DataFrame, expand='table').value)
    #        a    b
    # 0.0  1.0  2.0
    # 1.0  3.0  4.0
    return wb

def getMethod3():
    app = xw.App(visible=True)  # 隐藏Excel
    wb = app.books.open('test5.xlsx')  # 打开工作簿
    sht = wb.sheets['Sheet1']  # 实例化工作表
    for cell in list(map(''.join, product('ABCDEFGH', '1'))):  # A1 B1 C1 D1 E1 F1 G1 H1
        print(cell, sht.range(cell).color)  # 读取颜色
    for cell in list(map(''.join, product('ABCDEFGH', '3'))):  # A1 B1 C1 D1 E1 F1 G1 H1
        sht.range(cell).color=(255, 251, 100)# 填充颜色
    wb.save()
    return wb
    # A1 None
    # B1 (255, 0, 0)
    # C1 (255, 165, 0)
    # D1 (255, 255, 0)
    # E1 (0, 128, 0)
    # F1 (0, 255, 255)
    # G1 (0, 0, 255)
    # H1 (128, 0, 128)

def getMethod4():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    fig = plt.figure()
    plt.plot([1, 2, 3, 4, 5])
    sht.pictures.add(fig, name='MyPlot', update=True)
    wb.save('image.xlsx')
    return wb

def getMethod5():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    # sht.pictures.add('1.jpg')  # 使用相对路径可能报错
    sht.pictures.add(os.path.join(os.getcwd(), 'photo/123.webp'))
    wb.save('photo.xlsx')
    return wb

def getMethod6():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    rng = sht.range('A1')
    fileName = os.path.join(os.getcwd(), 'photo/123.webp')
    width, height = 10, 10  # 指定图片大小
    left = rng.left + (rng.width - width) / 2  # 居中
    top = rng.top + (rng.height - height) / 2
    sht.pictures.add(fileName, left=left, top=top, width=width, height=height)
    return wb

def getMethod7():
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    sht.range('A1', 'XFD1048576').api.Font.Name = '微软雅黑'  # 全局字体
    sht.range('A1').value = 'ABCDE'
    sht.range('A1').api.Font.Size = 12  # 字号
    sht.range('A1').api.Font.Bold = True  # 加粗
    start_index = 3  # 下标从1开始
    length_string = 1  # 修改长度
    sht.range('A1').api.GetCharacters(start_index, length_string).Font.Color = rgb_to_int((255, 0, 0))  # 设为红色

    sht.range('B1').value = 'ABCDE'
    sht.range('B1').api.Font.Italic = True  # 斜体
    sht.range('B1').api.Font.Strikethrough = True  # 删除线

    sht.range('C1').value = 'ABCDE'
    sht.range('C1').api.Font.Underline = True  # 下划线 4普通 5双下划线 -4119粗双下划线

    sht.range('A2').value = 'a2'
    sht.range('A2').api.GetCharacters(2, 1).Font.Superscript = True  # 上标

    sht.range('B2').value = 'H2O'
    sht.range('B2').api.GetCharacters(2, 1).Font.Subscript = True  # 下标

    sht.autofit()  # 自动调整
    return wb

def getMethod8():
        wb = xw.Book()
        sht = wb.sheets['Sheet1']

        '''5左上角对角线'''
        sht.range('A2', 'B3').api.Borders(5).LineStyle = 1  # 实线
        sht.range('A2', 'B3').api.Borders(5).Weight = 1  # 细
        sht.range('A2', 'B3').api.Borders(5).Color = 0x0  # 黑色

        '''6左下角对角线'''
        sht.range('C2', 'D3').api.Borders(6).LineStyle = -4119  # 双线
        sht.range('C2', 'D3').api.Borders(6).Weight = 2  # 细长
        sht.range('C2', 'D3').api.Borders(6).Color = 0xFF  # 红色

        '''7 8 9 10 左上下右'''
        for i in [7, 8, 9, 10]:
            sht.range('E2', 'F3').api.Borders(i).LineStyle = 4  # 点划相间线
            sht.range('E2', 'F3').api.Borders(i).Weight = 4  # 粗
            sht.range('E2', 'F3').api.Borders(i).Color = 0xFF00  # 绿色

        '''11内部垂直线'''
        sht.range('G2', 'H3').api.Borders(11).LineStyle = 5  # 划线后跟两个点
        sht.range('G2', 'H3').api.Borders(11).Weight = -4138  # 中
        sht.range('G2', 'H3').api.Borders(11).Color = rgb_to_int((0, 128, 128))  # 紫色

        '''12内部水平线'''
        sht.range('I2', 'J3').api.Borders(12).LineStyle = -4115  # 虚线
        sht.range('I2', 'J3').api.Borders(12).Weight = 4  # 粗
        sht.range('I2', 'J3').api.Borders(12).Color = rgb_to_int((0, 0, 255))  # 蓝色

        return wb

def getMethod9():
    wb = xw.Book()
    sht = wb.sheets.active
    sht.range('A1:D5').merge()  # 合并单元格
    input('Enter to unmerge')
    sht.range('A1:D5').unmerge()  # 拆分单元格
    input('Enter to quit')
    wb.close()

def getMethod10():
    wb = xw.Book()
    sht = wb.sheets.active
    sht.range('A1').value = [['语文', '数学', '总分'], [100, 100, None]]
    sht.range('C2').formula = '=SUM(A2:B2)'
    return wb

def getMethod11():
    wb = xw.Book()
    sht = wb.sheets.active
    _range = sht.range('A1', sht.used_range.last_cell)  # 用过的最后一个单元格
    _range = sht.range('A1', 'XFD1048576')  # 全局单元格
    return wb


if __name__ == '__main__':
    #wb=getMethod()#字典
    #wb=getMethod1()#Numpy数组
    #wb=getMethod2()#Pandas数据表
    #wb=getMethod3()#填充颜色
    #wb=getMethod4()#填充图片
    #wb=getMethod5()#填充图片
    #wb=getMethod6()#填充图片 位置调整
    #wb=getMethod7()#填充字体格式
    #wb=getMethod8()#填充边框格式
    #wb=getMethod9()#合并单元格
    #wb=getMethod10()#插入公式
    wb=getMethod11()#全局单元格
    input('输入任意键退出')
    wb.close()


